#coding:utf-8
import mysql.connector,time
import jieba.analyse,re
from spider.conf import conf

class db:
    table = "resultnews"
    type = "新闻"
    def __init__(self):
        self.conn = mysql.connector.connect(user=conf.USERNAME, password=conf.PASSWORD, database=conf.DATABASE, use_unicode=True)
        self.cursor = self.conn.cursor()
    def __del__(self):
        self.cursor.close()
        self.conn.close()
    def getKeywords(self):
        self.cursor.execute("SELECT distinct keyword from keywords where username in (select distinct username from monitorurl where type='"+self.type+"')")
        return self.cursor.fetchall()
    def getSiteKeywords(self):
        self.cursor.execute("select distinct monitorurl.url,keywords.keyword from keywords,monitorurl where keywords.username = monitorurl.username and monitorurl.type='"+self.type+"'")
        return self.cursor.fetchall()
    def updateKeyword(self,key):
        self.cursor.execute("update keywords set updatetime=now() where keyword=%s",[key])
        self.conn.commit()
    def getRows(self,username):
        return [];
    def makeResult(self):
        self.cursor.execute("select username from userinfo")
        rows = self.cursor.fetchall()
        for row in rows:
            self.makeOneResult(row[0])
            self.countNum(row[0])
            self.updateHeatTrend(row[0])
            self.updateSpider(row[0])
    def updateSpider(self,username):
        self.cursor.execute("update spider set updatetime=now() where type=%s and username=%s",[self.table[6:],username])
        self.conn.commit()
    def makeOneSstv(self,username,params,content):
        self.cursor.execute("select sstvword from sstvwords where username=%s",[username])
        rows = self.cursor.fetchall()
        sstvwords = ""
        for row in rows:
            if content.find(row[0])>=0:
                sstvwords+=row[0]+","
        if sstvwords != "":
            params.append(sstvwords)
            self.cursor.execute("insert resultsstv"+self.table[6:]+" (username,urlname,keyword,title,contenturl,contentkeyword,publishtime,insertdate,sstvword) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)",params)
            self.conn.commit()
    def makeOneResult(self,username):
        rows = self.getRows(username)
        for row in rows:
            params = [username]
            params.append(row[0])
            params.append(row[1])
            params.append(row[2])
            params.append(row[3])
            content = self.extractContent(row[4])
            params.append(self.extractKeyword(content))
            params.append(time.strftime("%Y-%m-%d",time.localtime(float(row[5]))))
            params.append(row[6])
            try:
                self.cursor.execute("insert "+self.table+" (username,urlname,keyword,title,contenturl,contentkeyword,publishtime,insertdate) values(%s,%s,%s,%s,%s,%s,%s,%s)",params)
                self.conn.commit()
            except mysql.connector.errors.IntegrityError:
                print(self.table+":主键重复")
                continue
            self.makeOneSstv(username,params,content)
    def updateHeatTrend(self,username):
        self.cursor.execute("select count(*) from heattrend where type='"+self.type+"' and username=%s",[username])
        if self.cursor.fetchone()[0]<1:
            self.cursor.execute("insert heattrend (username,type) values(%s,'"+self.type+"')",[username])
            self.conn.commit()
        params = []
        self.cursor.execute("select count(*) from "+self.table+" where username=%s and insertdate>DATE_SUB(now(),interval 1 day)",[username])
        params.append(self.cursor.fetchone()[0])
        self.cursor.execute("select count(*) from "+self.table+" where username=%s and insertdate>DATE_SUB(now(),interval 2 day) and insertdate<DATE_SUB(now(),interval 1 day)",[username])
        params.append(self.cursor.fetchone()[0])
        self.cursor.execute("select count(*) from "+self.table+" where username=%s and insertdate>DATE_SUB(now(),interval 3 day) and insertdate<DATE_SUB(now(),interval 2 day)",[username])
        params.append(self.cursor.fetchone()[0])
        self.cursor.execute("select count(*) from "+self.table+" where username=%s and insertdate>DATE_SUB(now(),interval 4 day) and insertdate<DATE_SUB(now(),interval 3 day)",[username])
        params.append(self.cursor.fetchone()[0])
        self.cursor.execute("select count(*) from "+self.table+" where username=%s and insertdate>DATE_SUB(now(),interval 5 day) and insertdate<DATE_SUB(now(),interval 4 day)",[username])
        params.append(self.cursor.fetchone()[0])
        self.cursor.execute("select count(*) from "+self.table+" where username=%s and insertdate>DATE_SUB(now(),interval 6 day) and insertdate<DATE_SUB(now(),interval 5 day)",[username])
        params.append(self.cursor.fetchone()[0])
        self.cursor.execute("select count(*) from "+self.table+" where username=%s and insertdate>DATE_SUB(now(),interval 7 day) and insertdate<DATE_SUB(now(),interval 6 day)",[username])
        params.append(self.cursor.fetchone()[0])
        params.append(username)
        self.cursor.execute("update heattrend set pre0=%s,pre1=%s,pre2=%s,pre3=%s,pre4=%s,pre5=%s,pre6=%s where type='"+self.type+"' and username=%s",params)
        self.conn.commit()
    def countNum(self,username):
        self.cursor.execute("select count(*) from num where username=%s",[username])
        if self.cursor.fetchone()[0]<1:
            self.cursor.execute("insert num (username) values(%s)",[username])
            self.conn.commit()
        params = []
        self.cursor.execute("select count(*) from resultnews where username=%s and insertdate>DATE_SUB(now(),interval 1 day)",[username])
        params.append(self.cursor.fetchone()[0])
        self.cursor.execute("select count(*) from resultluntan where username=%s and insertdate>DATE_SUB(now(),interval 1 day)",[username])
        params.append(self.cursor.fetchone()[0])
        self.cursor.execute("select count(*) from resultboke where username=%s and insertdate>DATE_SUB(now(),interval 1 day)",[username])
        params.append(self.cursor.fetchone()[0])
        self.cursor.execute("select count(*) from resultweixin where username=%s and insertdate>DATE_SUB(now(),interval 1 day)",[username])
        params.append(self.cursor.fetchone()[0])
        self.cursor.execute("select count(*) from resultnews where username=%s and insertdate<=DATE_SUB(now(),interval 1 day)",[username])
        params.append(self.cursor.fetchone()[0])
        self.cursor.execute("select count(*) from resultluntan where username=%s and insertdate<=DATE_SUB(now(),interval 1 day)",[username])
        params.append(self.cursor.fetchone()[0])
        self.cursor.execute("select count(*) from resultboke where username=%s and insertdate<=DATE_SUB(now(),interval 1 day)",[username])
        params.append(self.cursor.fetchone()[0])
        self.cursor.execute("select count(*) from resultweixin where username=%s and insertdate<=DATE_SUB(now(),interval 1 day)",[username])
        params.append(self.cursor.fetchone()[0])
        params.append(username)
        self.cursor.execute("update num set tdnewsnum=%s,tdluntannum=%s,tdbokenum=%s,tdweixinnum=%s,prenewsnum=%s,preluntannum=%s,prebokenum=%s,preweixinnum=%s where username=%s",params)
        self.conn.commit()
    def extractContent(self,content):
        content = re.subn('<style[\s\S]+?</style>','',content)[0]
        content = re.subn('<script[\s\S]+?</script>','',content)[0]
        content = re.subn('<!--[\s\S]+?-->','',content)[0]
        return re.subn('[a-z0-9A-Z\s,<.>/?;:\'"\[{\]}+-_=`~!@#$%^&*()]+',' ',content)[0]
    def extractKeyword(self,content):
        tags = jieba.analyse.extract_tags(content,4)
        return ",".join(tags)